GROUPING[_ID]

Purpose

With the use of this function you can distinguish between regular result rows and super-aggregate rows which are created in case of GROUPING SETS, CUBE or ROLLUP clauses.

Usage Notes

• Each argument must be similar to an expression within the GROUP BY clause.
• In case of a single argument the result value is 0 if the corresponding grouping considers this expression, otherwise 1 (superaggregation).
• In case of multiple arguments the result value is a number whose binary representation is similar to GROUPING(arg1), GROUPING(arg2),..., GROUPING(argn). For example, the following is true:
  GROUPING(a,b,c) = 4xGROUPING(a) + 2xGROUPING(b) + 1xGROUPING(c)
• For more information on GROUPING SETS, CUBE and ROLLUP, refer to the SELECT statement in the Query Language (DQL) section.

Example

SELECT SUM(volume) revenue, y, m,
DECODE(GROUPING(y,m),1,'yearly',3,'total',NULL) superaggregate
FROM sales GROUP BY ROLLUP(y,m) ORDER BY y,revenue;

Result